﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using MySql.Data.MySqlClient;
using MySql.Data.Common;
using MySql.Data.Types;
using ComponenteEntidad;
using System.Data.Common;
using System.Data;
using ComponenteConfiguracion;




namespace ComponenteDatos
{
    
    /// <summary>
    /// clase de operaciones de la entidad Pais
    /// </summary>
    public class DL_Pais
    {

        /// <summary>
        /// Variables Miembro
        /// </summary>
        private static DL_Pais instance;
        private string strConexion ;


        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="ConnectionString"></param>
        public DL_Pais()
        {
             this.strConexion = AppSettings.GetConnectionString;

         }

      
        /// <summary>
        /// Singleton
        /// </summary>
        public static DL_Pais Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new DL_Pais();
                }
                return instance;
            }
        }


        #region "Metodos Publicos"
      
        /// <summary>
        /// Metodo que obtine la lista de paises
        /// </summary>
        /// <returns></returns>
        public List<E_Pais> Obtener()
        {
           
            // Se crea Objeto de conexión con la base de datos
            MySqlConnection conec = new MySqlConnection(this.strConexion);
            // Se ejecuta consulta SQL
            MySqlCommand objectCommand = new MySqlCommand();
            objectCommand.CommandType = CommandType.StoredProcedure;
            objectCommand.Connection = conec;
            objectCommand.CommandText = "obtenerPaisbyId";

            // Se abre la Conexion
            conec.Open();
            // Todos los resultados de la consulta se cargan a un DataReader
            MySqlDataReader aReader = objectCommand.ExecuteReader();
            // Se crea DataTable
            DataTable objectDT = new DataTable();
            List<E_Pais> lstpais = new List<E_Pais>();

            try
            {
                // Se carga DataTable con los resultados de la consulta
                objectDT.Load(aReader);
         
                if (objectDT.Rows.Count > 0)
                {
                    foreach (DataRow itemPais in objectDT.Rows)
                    {
                       
                        E_Pais objE_Pais = new E_Pais();

                        objE_Pais.Code = itemPais[0].ToString();
                        objE_Pais.Name = itemPais[1].ToString();
                        objE_Pais.Continent = itemPais[2].ToString();
                        objE_Pais.Region = itemPais[3].ToString();
                        objE_Pais.LocalName = itemPais[4].ToString();
                        lstpais.Add(objE_Pais);
                        
                    }
                }


                return lstpais;
            }

            catch (Exception ex)
            {
                // Si la conexion falla, se cierra la conexion
                conec.Close();
                return null;
            }
            finally
            {
                // se cierra la conexion
                conec.Close();

            }





























            //mysqlConnetion = new MySqlConnection();
            //mysqlConnetion.ConnectionString = AppSettings.GetConnectionString;
            //cmd = new MySqlCommand();
            //cmd.CommandText = CommandType.StoredProcedure.ToString();
            //cmd.Connection = mysqlConnetion;
            //cmd.CommandText = "obtenerPaisbyId";

            //List<E_Pais> lstUsuarios = new List<E_Pais>();
            //E_Pais objE_pais = new E_Pais(); 
            //DataTable dt = new DataTable();

            //Database db = DatabaseFactory.CreateDatabase();
            ////DbCommand command = db.GetStoredProcCommand("obtenerPaisbyId");
            ////cmd = db.GetStoredProcCommand("obtenerPaisbyId");

            //cmd.CommandTimeout = 600;
            

            //mysqlConnetion.Open();
            //MySqlDataReader mysqlreader   = cmd.ExecuteReader();



            //while (mysqlreader.Read())
            //{
            //    lstUsuarios.Add(new E_Pais
            //    {
            //        Code = Convert.ToInt32(mysqlreader["Code"]),
            //        Name = mysqlreader["Name"].ToString(),
            //        Continent = mysqlreader["Continent"].ToString(),
            //        Region = mysqlreader["Region"].ToString(),
            //        LocalName = mysqlreader["LocalName"].ToString(),
            //      });
            //}

        //    if (dt.Rows.Count > 0)
        //        {
        //        foreach (DataRow itemUsuario in dt.Rows)
        //        {
        //        lstUsuarios.Add(new E_Pais{ Code = Convert.ToInt32(itemUsuario["Code"]),
        //                                    Name = itemUsuario["Name"].ToString(),
        //                                    Continent = itemUsuario["Continent"].ToString(),
        //                                    Region = itemUsuario["Region"].ToString(),
        //                                    LocalName = itemUsuario["LocalName"].ToString(),});
        //        }
        //}


        }

        #endregion
    }
}
